"""
单独创建UnitConversion表
此脚本专门用于创建单位转换规则表
"""
import os
import sys
from pathlib import Path
import logging
import sqlite3
from datetime import datetime

# 设置日志
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

# 添加项目根目录到Python路径
current_dir = os.path.dirname(os.path.abspath(__file__))
project_root = str(Path(current_dir).absolute())
sys.path.insert(0, project_root)

# 获取数据库文件路径
DB_PATH = os.path.join(project_root, "backend", "twrpa.db")
if not os.path.exists(DB_PATH):
    DB_PATH = os.path.join(project_root, "backend", "app.db")
    if not os.path.exists(DB_PATH):
        # 尝试查找其他可能的数据库文件
        possible_dbs = []
        for file in os.listdir(os.path.join(project_root, "backend")):
            if file.endswith(".db"):
                possible_dbs.append(os.path.join(project_root, "backend", file))
        
        if possible_dbs:
            DB_PATH = possible_dbs[0]
            logger.info(f"使用找到的数据库: {DB_PATH}")
        else:
            logger.error("找不到数据库文件!")
            sys.exit(1)

logger.info(f"数据库路径: {DB_PATH}")

def create_unit_conversion_table():
    """直接使用SQLite创建单位转换表"""
    try:
        # 连接到SQLite数据库
        conn = sqlite3.connect(DB_PATH)
        cursor = conn.cursor()
        
        # 检查表是否已存在
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='unit_conversion'")
        if cursor.fetchone():
            logger.info("unit_conversion表已存在")
        else:
            # 创建表
            cursor.execute('''
            CREATE TABLE unit_conversion (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                source_unit VARCHAR(50) NOT NULL,
                target_unit VARCHAR(50) NOT NULL,
                conversion_formula VARCHAR(255) NOT NULL,
                description TEXT,
                is_active BOOLEAN DEFAULT 1,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
            ''')
            logger.info("成功创建unit_conversion表")
        
        # 添加默认数据
        default_conversions = [
            # 重量单位转换为"斤"
            ("g", "斤", "x / 500", "克转斤"),
            ("克", "斤", "x / 500", "克转斤"),
            ("kg", "斤", "x * 2", "千克转斤"),
            ("千克", "斤", "x * 2", "千克转斤"),
            ("公斤", "斤", "x * 2", "公斤转斤"),
            ("斤", "斤", "x", "斤转斤"),
            
            # 体积单位转换为"升"
            ("ml", "升", "x / 1000", "毫升转升"),
            ("毫升", "升", "x / 1000", "毫升转升"),
            ("l", "升", "x", "升转升"),
            ("L", "升", "x", "升转升"),
            ("升", "升", "x", "升转升"),
            
            # 计数单位保持不变
            ("个", "个", "x", "个转个"),
            ("件", "件", "x", "件转件"),
            ("包", "包", "x", "包转包"),
            ("袋", "袋", "x", "袋转袋"),
            ("箱", "箱", "x", "箱转箱"),
            ("瓶", "瓶", "x", "瓶转瓶"),
            ("罐", "罐", "x", "罐转罐"),
            ("盒", "盒", "x", "盒转盒"),
            ("片", "片", "x", "片转片")
        ]
        
        for conv in default_conversions:
            # 检查是否已存在
            cursor.execute(
                "SELECT id FROM unit_conversion WHERE source_unit=? AND target_unit=?", 
                (conv[0], conv[1])
            )
            if cursor.fetchone() is None:
                now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                cursor.execute(
                    """
                    INSERT INTO unit_conversion 
                    (source_unit, target_unit, conversion_formula, description, is_active, created_at, updated_at) 
                    VALUES (?, ?, ?, ?, 1, ?, ?)
                    """, 
                    (conv[0], conv[1], conv[2], conv[3], now, now)
                )
                logger.info(f"添加转换规则: {conv[0]} -> {conv[1]}")
        
        # 提交更改
        conn.commit()
        
        # 输出表内容
        cursor.execute("SELECT * FROM unit_conversion")
        rows = cursor.fetchall()
        logger.info(f"unit_conversion表中有 {len(rows)} 条记录")
        for row in rows:
            logger.info(f"记录: {row}")
        
        conn.close()
        return True
    except Exception as e:
        logger.error(f"创建unit_conversion表失败: {str(e)}")
        import traceback
        logger.error(traceback.format_exc())
        return False

if __name__ == "__main__":
    logger.info("开始创建unit_conversion表...")
    if create_unit_conversion_table():
        logger.info("成功完成!")
    else:
        logger.error("操作失败!")
        sys.exit(1) 